package testes.vSys.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;

import org.hsqldb.Types;

import com.sun.jmx.snmp.Timestamp;

import testes.vSys.Constants;
import testes.vSys.bean.EventType;
import testes.vSys.bean.Moviment;
import testes.vSys.bean.Vehicle;
import testes.vSys.database.DatabaseConnection;
import testes.vSys.database.DatabaseController;
import testes.vSys.database.NullParameter;

public class _hsql_MovimentDao implements IMovimentDao {

	public Moviment createMoviment(Moviment moviment) {

		DatabaseConnection conn = null;
		Moviment newMoviment = null;

		if (moviment == null) return null;

        try {
            conn = DatabaseController.getConnection();

            // TODO verificar como pegar o id da sequecia no oracle
            String sql = "INSERT INTO VS_EVENT ( event_date, event_value, event_type, vehicle) " +
            		"VALUES ( ?, ?, ?, ?);";

            Integer movimentId = null;

            movimentId = conn.executeUpdate(sql, new Object[] {	
            		moviment.getDate() == null ? new NullParameter(Types.DATE) : moviment.getDate(),
            	    new Double(moviment.getValue()) == null ? new NullParameter(Types.REAL) : moviment.getValue(),
            	    moviment.getEventType() == null ? new NullParameter(Types.VARCHAR) : "".equals(""+moviment.getEventType().getEventType()) ? new NullParameter(Types.VARCHAR): moviment.getEventType().getEventTypeId(),
            	    moviment.getVehicle() == null ? new NullParameter(Types.BIGINT) : new Integer(moviment.getVehicle().getId()) == null ? new NullParameter(Types.BIGINT) : moviment.getVehicle().getId()            		
            });
            
            ResultSet rs = conn.executeQuery ("CALL IDENTITY();");
            while (rs.next()){
            	movimentId = rs.getInt(1);
            }
            
            if (movimentId != null) {
            	newMoviment = fillMoviment(moviment, movimentId);             	
            }
            
        } catch (Exception e) {
        	
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		
		return newMoviment;	
	}

	public Collection<Moviment> getMovimentBetween(Date start, Date end) {
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Moviment moviment = null;
		List<Moviment> moviments = new ArrayList<Moviment>();

        try {
            conn = DatabaseController.getConnection();

            String sql = "SELECT * FROM vs_event WHERE event_date between ? and ?;";
            rs = conn.executeQuery(sql, new Object[] {start, end});
        
            while (rs.next()){     
            	moviment = fillMoviment(rs);
            	moviments.add(moviment);
            }

        } catch (Exception e) {
        	
        } finally {
			if (conn != null) {
				conn.close();
			}
		}
		return moviments;
	}

	public Collection<Moviment> getMovimentByVehicle(Vehicle vehicle) {
		
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Collection<Moviment> moviments = new ArrayList<Moviment>();

		try{
			conn = DatabaseController.getConnection();
			String sql = "" +
				" SELECT m.* " +
				" FROM " +
				"	vs_event m," +
				"	vs_vehicle v " + 
				" WHERE " +
			    " m.vehicle = v.id_vehicle ";
				if (!"".equals(vehicle.getPlate()) || 
						vehicle.getPlate() != null){
					sql += 	" AND	v.plate = '" +vehicle.getPlate() + "' ";
				}
				if (vehicle.getId() != Constants.VSYS_UNKNOWN_ID){
					sql += 	" AND	m.vehicle = " + vehicle.getId();
				}
				sql += ";";
				

			rs = conn.executeQuery(sql);
			
			while(rs.next()){
				moviments.add(fillMoviment(rs));
			}
						
		}catch(Exception e){
			
			return null;
		}finally{
			if(conn != null)
				conn.close();
		}
		
		return moviments;
	}

	public Collection<EventType> getAvaiableEvents() {
		DatabaseConnection conn = null;
		ResultSet rs = null;
		List<EventType> eventTypes = new ArrayList<EventType>();
		EventType eventType = null;

        try {
            conn = DatabaseController.getConnection();

           String sql = "SELECT * FROM vs_event_type;";
            rs = conn.executeQuery(sql);
        
            while (rs.next()){
            	eventType = new EventType(rs.getInt("id_event_type"));
            	eventType.setEventType(rs.getString("event_type_name"));
            	eventTypes.add(eventType);
            }

        } catch (Exception e) {
        	
        } finally {
			if (conn != null) {
				conn.close();
			}
		}
		return eventTypes;
	}

	public boolean removeMoviment(int movimentId) {
		boolean result = false;
		DatabaseConnection conn = null;

        try {
            conn = DatabaseController.getConnection();

            String sql = "DELETE FROM vs_event WHERE id_event = ?; ";
            int i = conn.executeUpdate(sql, new Object[] { movimentId });

            // assumindo que é chave (volta apenas 1)
            if (i == 1){
            	result = true;
            }
            
        } catch (Exception e) {
        	
        } finally {
			if (conn != null) {
				conn.close();
			}
		}

        return result;
    }

	public boolean updateMoviment(Moviment moviment) {
		DatabaseConnection conn = null;
		boolean result = false;

		if (moviment == null) return false;

        try {
            conn = DatabaseController.getConnection();

            String sql = "UPDATE vs_Event SET event_date = ?, event_value = ?, event_type = ?, vehicle = ? " +
            		"WHERE id_event = ?; ";

            int i = conn.executeUpdate(sql, new Object[] {	
            		moviment.getDate() == null ? new NullParameter(Types.DATE) : moviment.getDate(),
                 	new Double(moviment.getValue()) == null ? new NullParameter(Types.REAL) : moviment.getValue(),
                    moviment.getEventType() == null ? new NullParameter(Types.VARCHAR) : "".equals(""+moviment.getEventType().getEventType()) ? new NullParameter(Types.VARCHAR): moviment.getEventType().getEventType(),
                    moviment.getVehicle() == null ? new NullParameter(Types.BIGINT) : new Integer(moviment.getVehicle().getId()) == null ? new NullParameter(Types.BIGINT) : moviment.getVehicle().getId()            		
            });

            // assumindo que é chave (volta apenas 1)
            if (i == 1) {
            	result = true;        	
            }
            
        } catch (Exception e) {
        		e.printStackTrace();
        	
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return result;
		
	}

	private Moviment fillMoviment(ResultSet rs) {
		Moviment moviment = null;
		EventType eventType = null;
		Vehicle vehicle = null;
		
		try {
			moviment = new Moviment(rs.getInt("id_event"));
			moviment.setDate(rs.getDate("event_date"));
			moviment.setValue(rs.getDouble("event_value"));
			
			eventType = this.getEvent(rs.getInt("event_type"));
			moviment.setEventType(eventType);
			
			IVehicleDao vehicleDAO = DAOFactory.getVehicleDao();
			vehicle = vehicleDAO.getVehicleById(rs.getInt("vehicle"));
			moviment.setVehicle(vehicle);
			
		} catch (Exception e) {
			
    	}

		return moviment;
	}
	
	
	private Moviment fillMoviment(Moviment oldMoviment, int movimentId) {
		Moviment moviment = null;
		
		try {
			moviment = new Moviment(movimentId);
			moviment.setDate(oldMoviment.getDate());
			moviment.setValue(oldMoviment.getValue());
			moviment.setEventType(oldMoviment.getEventType());
			moviment.setVehicle(oldMoviment.getVehicle());
		} catch (Exception e) {
			
    	}

		return moviment;
	}
	
	
	public Collection<Moviment> getMoviment(Moviment moviment) {
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Collection<Moviment> moviments = new ArrayList<Moviment>();

		try{
			conn = DatabaseController.getConnection();
			String sql = "" +
				" SELECT * " +
				" FROM " +
				"	vs_event m" +
				" WHERE 1 = 1 ";
				if (!"".equals(moviment.getEventType()) || 
						moviment.getEventType() != null){
					sql += 	" AND	m.event_type = " + moviment.getEventType() + " ";
				}
				if (moviment.getId() != Constants.VSYS_UNKNOWN_ID){
					sql += 	" AND	m.id_event = " + moviment.getId();
				}
				sql += ";";
				
				System.out.println( "[IVASSE] check: " + sql );
				

			rs = conn.executeQuery(sql);
			
			while(rs.next()){
				moviments.add( fillMoviment(rs) );
			}
						
		}catch(Exception e){
			
			return null;
		}finally{
			if(conn != null)
				conn.close();
		}
		
		return moviments;
	}

	public EventType getEvent(int id) {
		DatabaseConnection conn = null;
		ResultSet rs = null;
		EventType eventType = null;
		
		if (id == Constants.VSYS_UNKNOWN_ID){
			return null;
		} else {

			try{
				conn = DatabaseController.getConnection();
				String sql = "" +
					" SELECT e.* " +
					" FROM vs_event_type e" +
					" WHERE e.id_event_type = " + id;
	
				rs = conn.executeQuery(sql);
				
				if (rs.next()){
					eventType = new EventType(rs.getInt("id_event_type"),rs.getString("event_type_name"));
				}
							
			}catch(Exception e){
				
				return null;
			}finally{
				if(conn != null)
					conn.close();
			}
			
			return eventType;
		}
	}


}
 
